SET NOCOUNT ON;
go
/*********************************************************************************************************
*Created/Source by	:sfibich
*ScriptName			:SQLAgent.jobs.twoHourJob.sql													
*Version			:1.0.120814
*Purpose			:This script creates a job that executes every two hours.  This is a core repeating
*					 job used to execute a number of task that need to take place every two hours.
*					 
*********************************************************************************************************/
BEGIN TRY
BEGIN TRAN

IF EXISTS (SELECT 1 FROM sys.procedures WHERE name='Utility_DB_Schedule_Job_Every_Two_Hours'
	and schema_id = schema_id('SQLAagent'))
BEGIN
	DROP PROCEDURE performance.Fill_Ring_Buffer_Scheduler_Monitor;
	delete metadata.utilityInfo 
	where
	info_desc='Utility_DB_Schedule_Job_Every_Two_Hours'
END

if exists (select 1 from msdb.dbo.sysjobs where name ='Utility_DB_Schedule_Job_Every_Two_Hours')
	BEGIN
		exec msdb.dbo.sp_delete_job @job_name='Utility_DB_Schedule_Job_Every_Two_Hours';
	END

exec msdb.dbo.sp_add_job 
@job_name='Utility_DB_Schedule_Job_Every_Two_Hours'
,@description='This job was created by the Utility DB Codeplex Project.  This job executes every 2 hours';

exec msdb.dbo.sp_add_jobstep 
@job_name=N'Utility_DB_Schedule_Job_Every_Two_Hours'
,@step_name=N'performance.Fill_Ring_Buffer_Scheduler_Monitor'
,@subsystem=N'TSQL'
,@command=N'exec Utility.performance.Fill_Ring_Buffer_Scheduler_Monitor'
,@on_success_action=1;

exec msdb.dbo.sp_add_jobschedule 
@job_name=N'Utility_DB_Schedule_Job_Every_Two_Hours'
,@name=N'Every Two Hours'
,@enabled=1
,@freq_type=4
,@freq_interval=1
,@freq_subday_type=8
,@freq_subday_interval=2

exec msdb.dbo.sp_add_jobserver 
@job_name=N'Utility_DB_Schedule_Job_Every_Two_Hours'
, @server_name = @@SERVERNAME 

exec msdb.dbo.sp_attach_schedule 
@job_name=N'Utility_DB_Schedule_Job_Every_Two_Hours'
,@schedule_name=N'Every Two Hours'

if exists (select 1 from sys.objects where name='Utility_DB_Schedule_Job_Every_Two_Hours' and SCHEMA_NAME(schema_id)='SQLAgent')
BEGIN
	merge metadata.utilityInfo as t
	using (
	select 
	'Utility_DB_Schedule_Job_Every_Two_Hours' as info_desc
	,'1.0.120814' as value
	) as s
	on
	(s.info_desc=t.info_desc)
	when matched then
	update 
	set 
	t.value=s.value
	,t.date_time=getdate()
	when not matched then 
	insert
	(info_desc,value)
	values
	(s.info_desc,s.value);
END

COMMIT TRAN
END TRY
BEGIN CATCH
	PRINT 'ERROR_NUMBER:'+cast(ERROR_NUMBER() as varchar(10))
    PRINT 'ERROR_MESSAGE:'+ERROR_MESSAGE() 

	if @@TRANCOUNT >0 
		ROLLBACK TRAN
END CATCH	
